import { QueryTypes } from "@sequelize/core";
import { Column, Table } from "../../model/schema";
import { QuerySchema } from "../QuerySchema";

export class PostgreSQLSchema extends QuerySchema{
    
    constructor(){
        super('postgresql');
        this.tablesSql = "select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0)";
        // this.tableFieldsSql = "SELECT A.attname AS name,format_type (A.atttypid,A.atttypmod) AS type,col_description (A.attrelid,A.attnum) AS comment, (CASE WHEN (SELECT COUNT (*) FROM pg_constraint AS PC WHERE A.attnum = PC.conkey[1] AND PC.contype = 'p') > 0 THEN 'PRI' ELSE '' END) AS key  FROM pg_class AS C,pg_attribute AS A WHERE A.attrelid='%s'::regclass AND A.attrelid= C.oid AND A.attnum> 0 AND NOT A.attisdropped ORDER  BY A.attnum";
        this.tableFieldsSql = `SELECT 
        A.attname as field,
        (select description from pg_description d where d.objoid = A.attrelid AND d.objsubid = A.attnum ) AS COMMENT,
        concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\\(.*\\)' ) ) AS type,
        A.attnotnull as notnull,
        (CASE 
            WHEN ( SELECT COUNT ( * ) FROM pg_constraint AS PC WHERE A.attnum = PC.conkey [ 1 ] AND PC.contype = 'p' ) > 0 THEN 'PRI'
          WHEN ( SELECT COUNT ( * ) FROM pg_constraint AS PC WHERE A.attnum = PC.conkey [ 1 ] AND PC.contype = 'u' ) > 0 THEN 'UNI' ELSE'' 
            END) AS key,
        cols.column_default as defaultValue
    FROM
        pg_class C,
        pg_attribute A,
        pg_type T,
        information_schema.columns cols
    WHERE
        A.attnum > 0 
        AND A.attrelid = C.oid 
        AND A.atttypid = T.oid 
        AND C.relname IN ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND POSITION ( '_2' IN tablename ) = 0 ) 
        AND cols.table_name = C.relname
        AND cols.column_name = A.attname
        AND C.relname = '%s'
    ORDER BY
        C.relname,
        A.attnum`;
        this.tableName = "tabname";
        this.tableComment = "comment";
        this.fieldName = "name";
        this.fieldType = "type";
        this.fieldComment = "comment";
        this.fieldKey = "key";
        this.fieldDefaultValue = "defaultValue";
    }

    parseTableFromDB({tabname, comment}){
        return new Table(tabname, comment);
    }
    
    parseColumnFromDB({field, type, notnull, key, defaultValue, comment}){
        return new Column(
            field,
            type,
            defaultValue,
            key.indexOf('PRI') > -1,
            key.indexOf('UNI') > -1,
            notnull,
            comment
        )
    }
}